USE [NANS21V]
GO

/****** オブジェクト:  StoredProcedure [dbo].[ResultEkidenTeam]    スクリプト日付: 11/14/2024 11:30:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ResultEkidenTeam]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ResultEkidenTeam]
GO

CREATE PROCEDURE [dbo].[ResultEkidenTeam]
	@TAIKAINO int,
	@KYOGINO int,
	@SUBNO int,
    @ROUNDNO int,
	@KUMINO int,
	@TEAMNO int
AS
BEGIN

select ORD.LANE
    ,  ORD.SOUJUN
    ,  lap.ID
    ,  runner.KYOGISYANO
    ,  runner.KYOGISYAMEI  AS MEI
    ,  runner.KYOGISYAKANA AS KANA
--    ,  FLOOR((stime.KIROKU + kiroku.KIROKU) - nows.KIROKU) AS KUKANKIROKU
    ,  CASE WHEN kiroku.KIROKU = 0 THEN 0 ELSE FLOOR((stime.KIROKU + kiroku.KIROKU) - nows.KIROKU) END AS KUKANKIROKU
    ,  kiroku.EXTEND3 AS KUKANJUNI
--    ,  FLOOR(ISNULL(
--        kiroku.KIROKU + ISNULL(dbo.ResultEkidenHosei(@TAIKAINO,@KYOGINO,(select LANE from TBL_LANE where KYOGISYANO = @TEAMNO),ORD.SOUJUN-1),0)  /* 補正調整中*/
--       ,(stime.KIROKU + kiroku.KIROKU) - nows.KIROKU )) AS TUUKAKIROKU /* 第一中継所では区間記録 */
    , CASE WHEN kiroku.KIROKU = 0 THEN 0 ELSE
       FLOOR(ISNULL(
        kiroku.KIROKU + ISNULL(dbo.ResultEkidenHosei(@TAIKAINO,@KYOGINO,(select LANE from TBL_LANE where KYOGISYANO = @TEAMNO),ORD.SOUJUN-1),0)  /* 補正調整中*/
       ,(stime.KIROKU + kiroku.KIROKU) - nows.KIROKU )) END AS TUUKAKIROKU /* 第一中継所では区間記録*/
    ,  kiroku.EXTEND4 AS TUUKAJUNI
    ,  (ISNULL(
        CASE WHEN (stime.KIROKU + kuriage.KIROKU) = nows.KIROKU
         THEN 0 ELSE CASE WHEN FLOOR((stime.KIROKU + kuriage.KIROKU) - nows.KIROKU) = stime.KIROKU 
         THEN 0 ELSE FLOOR((stime.KIROKU + kuriage.KIROKU) - nows.KIROKU) END 
        END
        ,0)
        ) AS KURIAGE /* 繰上(ここ以降に加算する補正値) */
    ,  comment.COMMENTRYAKU AS COMMENT
	,  comment.BUNRUI
    ,  comment.UTIWAKE
    ,  Rlap.KYOGIFLG2
    ,  Mlap.LAPMEI
    ,  kumi.ROLLCALLFLG
from TBL_ORDER as ORD /* 区間の情報 */

left join TBL_KUMI as kumi on ORD.TAIKAINO = kumi.TAIKAINO 
    and ORD.KYOGINO = kumi.KYOGINO

left join TBL_KYOGISYA as runner on ORD.TAIKAINO = runner.TAIKAINO /* 競技者情報 */
    and ORD.KYOGISYANO = runner.KYOGISYANO 
left join TBL_KYOGI as kyogi on ORD.TAIKAINO = kyogi.TAIKAINO /* SYUMOKUNO の経由地 */
    and ORD.KYOGINO = kyogi.KYOGINO
left join TBL_LAP as lap on kyogi.SYUMOKUNO = lap.SYUMOKUNO /* LAPNO の経由地 */
    and ORD.SOUJUN = lap.ID
left join TBL_RESULT_LAP as Rlap on lap.LAPNO = Rlap.LAPNO/* KYOGIFLG2 */
    and kyogi.TAIKAINO = Rlap.TAIKAINO and kyogi.KYOGINO = Rlap.KYOGINO
left join TBL_LAP_MASTER as Mlap on lap.LAPNO = Mlap.LAPNO

left join TBL_RESULT_SIGI as stime on ORD.TAIKAINO = stime.TAIKAINO /* 開始時間用 */
    and ORD.KYOGINO = stime.KYOGINO and ORD.LANE = stime.LANE 
    and stime.SIGINO = 1 
left join TBL_RESULT_SIGI as kuriage on ORD.TAIKAINO = kuriage.TAIKAINO /* 繰上判定用(前の通過記録) */
    and ORD.KYOGINO = kuriage.KYOGINO and ORD.LANE = kuriage.LANE 
    and (ORD.SOUJUN-1)*2 = kuriage.SIGINO
left join TBL_RESULT_SIGI as nows on ORD.TAIKAINO = nows.TAIKAINO /* 今の開始 */
    and ORD.KYOGINO = nows.KYOGINO and ORD.LANE = nows.LANE 
    and ORD.SOUJUN*2-1 = nows.SIGINO /* (ORD.SOUJUN-1)*2+1 でも可*/
left join TBL_RESULT_SIGI as kiroku on ORD.TAIKAINO = kiroku.TAIKAINO /* 通過記録 */
    and ORD.KYOGINO = kiroku.KYOGINO and ORD.LANE = kiroku.LANE 
    and ORD.SOUJUN*2 = kiroku.SIGINO 
left join (SELECT * FROM TBL_COMMENT WHERE BUNRUI='1') as comment on kiroku.EXTEND1 = comment.COMMENTNO

where ORD.TAIKAINO = @TAIKAINO and ORD.KYOGINO = @KYOGINO 
    and ORD.SUBNO  = @SUBNO    and ORD.ROUNDNO = @ROUNDNO 
    and ORD.KUMINO = @KUMINO   and ORD.LANE    = (select LANE from TBL_LANE where KYOGISYANO = @TEAMNO) 
order by ORD.SOUJUN ASC 

END